{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from splinter import Browser\n",
    "from bs4 import BeautifulSoup as bs\n",
    "import pandas as pd\n",
    "import time\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# open broswer for next output\n",
    "execute_path = {\"executable_path\": \"/usr/local/bin/chromedriver\"}\n",
    "browser = Browser(\"chrome\", **execute_path, headless = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>html</th>\n",
       "      <th>Name</th>\n",
       "      <th>Number</th>\n",
       "      <th>Type</th>\n",
       "      <th>Area (sqft)</th>\n",
       "      <th>EUI (kBTU/sqft)</th>\n",
       "      <th>LEED</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://www.oeis.ucf.edu/buildings/5</td>\n",
       "      <td>Alpha Delta Pi</td>\n",
       "      <td>406</td>\n",
       "      <td>Residence Hall</td>\n",
       "      <td>5477</td>\n",
       "      <td>270.56</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://www.oeis.ucf.edu/buildings/65</td>\n",
       "      <td>Alpha Tau Omega</td>\n",
       "      <td>410</td>\n",
       "      <td>Residence Hall</td>\n",
       "      <td>10000</td>\n",
       "      <td>61.70</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://www.oeis.ucf.edu/buildings/60</td>\n",
       "      <td>Alpha Xi Delta</td>\n",
       "      <td>404</td>\n",
       "      <td>Residence Hall</td>\n",
       "      <td>5200</td>\n",
       "      <td>534.42</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://www.oeis.ucf.edu/buildings/45</td>\n",
       "      <td>AMPAC Research Facility</td>\n",
       "      <td>152</td>\n",
       "      <td>Research</td>\n",
       "      <td>7432</td>\n",
       "      <td>276.40</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://www.oeis.ucf.edu/buildings/133</td>\n",
       "      <td>Ara Dr. Research Facility</td>\n",
       "      <td>117</td>\n",
       "      <td>Research</td>\n",
       "      <td>2720</td>\n",
       "      <td>375.14</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                     html                       Name  Number  \\\n",
       "0    https://www.oeis.ucf.edu/buildings/5             Alpha Delta Pi     406   \n",
       "1   https://www.oeis.ucf.edu/buildings/65            Alpha Tau Omega     410   \n",
       "2   https://www.oeis.ucf.edu/buildings/60             Alpha Xi Delta     404   \n",
       "3   https://www.oeis.ucf.edu/buildings/45    AMPAC Research Facility     152   \n",
       "4  https://www.oeis.ucf.edu/buildings/133  Ara Dr. Research Facility     117   \n",
       "\n",
       "             Type  Area (sqft)  EUI (kBTU/sqft) LEED  \n",
       "0  Residence Hall         5477           270.56  NaN  \n",
       "1  Residence Hall        10000            61.70  NaN  \n",
       "2  Residence Hall         5200           534.42  NaN  \n",
       "3        Research         7432           276.40  NaN  \n",
       "4        Research         2720           375.14  NaN  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get the table in url_new and add the linker/url for all data\n",
    "url_news = 'https://www.oeis.ucf.edu/buildings'\n",
    "browser.visit(url_news)\n",
    "html = browser.html\n",
    "soup = bs(html, 'html.parser')\n",
    "time.sleep(1)\n",
    "first_bs = soup.find('table', {'id': 'buildings'})\n",
    "df_list = []\n",
    "for row in first_bs.find_all('tr'):\n",
    "    for td in row.find_all('a'):\n",
    "        df_list.append({'html': td['href'], 'Name': td.text[1:-1]})\n",
    "df_a = pd.DataFrame(df_list)\n",
    "df_table = pd.read_html(url_news)\n",
    "df_real = pd.merge(df_a, df_table[0], on = 'Name', how = 'inner')\n",
    "df_real.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get the information of building id and square_feet to search for data\n",
    "df_building = pd.read_csv('../Resources/building_metadata.csv')\n",
    "sqf_list = list(df_building.loc[df_building['site_id'] == 0,'square_feet'])\n",
    "df_building = df_building.loc[df_building['site_id'] == 0,:].set_index('square_feet')\n",
    "df_reset = df_real.set_index('Area (sqft)')\n",
    "json_list = []\n",
    "df_electric = pd.DataFrame([], columns = ['timestamp', 'reading', 'building_id', 'meter'])\n",
    "df_water = pd.DataFrame([], columns = ['timestamp', 'reading', 'building_id', 'meter'])\n",
    "pop_index = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "105"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test how many information in the list\n",
    "real_list = list(df_real['Area (sqft)'])\n",
    "len(sqf_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# if the loop is crash, delete the run data in loop to rerun for saving time.\n",
    "temp_list = sqf_list[pop_index: ]\n",
    "# main loop to scrapping\n",
    "for test in temp_list:\n",
    "    print(pop_index)\n",
    "    # open browser\n",
    "    execute_path = {\"executable_path\": \"/usr/local/bin/chromedriver\"}\n",
    "    browser = Browser(\"chrome\", **execute_path, headless = False)\n",
    "    url_new = df_reset.loc[test,'html']\n",
    "    # test whether there is only one building having the area information\n",
    "    if_break = False\n",
    "    if type(url_new) != str:\n",
    "        if_break = True\n",
    "        # if not check the builting year\n",
    "        for i in url_new:\n",
    "            browser.visit(i)\n",
    "            time.sleep(1)\n",
    "            html2 = browser.html\n",
    "            soup2 = bs(html2, 'html.parser')\n",
    "            txt = soup2.find_all('div', {'class': \"col-sm-7\"})[0].find_all('div')[1].text\n",
    "            txt = txt[-4:]\n",
    "            if txt == str(df_building.loc[test, 'year_built']):\n",
    "                url_new = i\n",
    "                if_break = False\n",
    "                break\n",
    "    if if_break:\n",
    "        continue\n",
    "    browser.visit(url_new)\n",
    "    time.sleep(1)\n",
    "    html1 = browser.html\n",
    "    soup1 = bs(html1, 'html.parser')\n",
    "    # print to check whether information is correct\n",
    "    print('sqft: ' + str(test) + '; years: ' + str(df_building.loc[test, 'year_built']))\n",
    "    print(soup1.find_all('div', {'class': \"col-sm-7\"})[0].find_all('div')[1].text)\n",
    "    print('-'*35)\n",
    "    # fill the data and click get data to get the information. (click may not work)\n",
    "    browser.fill('start-date', '01/01/2017')\n",
    "    browser.fill('end-date', '01/01/2019')\n",
    "    browser.find_by_id('filetype').first.select('json')\n",
    "    try:\n",
    "        browser.find_by_value('Get Data').click()\n",
    "    except:\n",
    "        print('there is something wrong in ' + str(test))\n",
    "        break\n",
    "    # sleep enough time to avoid not loading enough\n",
    "    html = browser.html\n",
    "    time.sleep(3)\n",
    "    soup = bs(html, 'html.parser')\n",
    "    browser.quit()\n",
    "    # load json\n",
    "    try:\n",
    "        json_real = json.loads(soup.text)\n",
    "    except:\n",
    "        # if there is no information, just continue\n",
    "        print('there is no data in ' + str(test))\n",
    "        temp_list = sqf_list[pop_index: ]\n",
    "        continue\n",
    "    # try to make the dataframe\n",
    "    try:\n",
    "        df_temp_electric = pd.DataFrame(json_real[0]['values'])\n",
    "        df_temp_electric['building_id'] = df_building.loc[test, 'building_id']\n",
    "        df_temp_electric['meter'] = 0\n",
    "        df_electric = pd.concat([df_electric, df_temp_electric], ignore_index=True, sort=False)\n",
    "    except:\n",
    "        print('there is huge problem in ' + str(test))\n",
    "        break\n",
    "    try:\n",
    "        df_temp_water = pd.DataFrame(json_real[1]['values'])\n",
    "    except:\n",
    "        print('there is no water data in ' + str(test))\n",
    "    try:\n",
    "        df_temp_water['building_id'] = df_building.loc[test, 'building_id']\n",
    "        df_temp_water['meter'] = 5\n",
    "        df_water = pd.concat([df_water, df_temp_water], ignore_index=True, sort=False)\n",
    "    except:\n",
    "        print('there is huge problem of water data in ' + str(test))\n",
    "    print('*'*35)\n",
    "    print(str(df_electric.shape))\n",
    "    print(str(df_water.shape))\n",
    "    print('*'*35)\n",
    "    pop_index += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "electric_list = list(df_electric['building_id'].unique())\n",
    "electric_area_list = []\n",
    "for i in electric_list:\n",
    "    electric_area_list.append(df_building.index[df_building['building_id'] == i].values[0])\n",
    "rerun_list = []\n",
    "for i in sqf_list:\n",
    "    if i not in electric_area_list:\n",
    "        rerun_list.append(i)\n",
    "pop_index = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1\n",
      "there is no data in 59200\n",
      "2\n",
      "there is no data in 38686\n",
      "3\n",
      "there is no data in 42731\n",
      "4\n",
      "there is no data in 41617\n",
      "5\n",
      "there is no data in 53130\n",
      "6\n",
      "there is no data in 53130\n",
      "7\n",
      "sqft: 59071; years: 1980.0\n",
      "59071 sqft Retail building, constructed in 1980\n",
      "-----------------------------------\n",
      "there is no data in 59071\n",
      "7\n",
      "there is no data in 24456\n",
      "8\n",
      "there is no data in 387638\n",
      "9\n",
      "there is no data in 38686\n",
      "10\n",
      "there is no data in 42731\n",
      "11\n",
      "there is no data in 41617\n",
      "12\n",
      "there is no data in 53130\n",
      "13\n",
      "there is no data in 53130\n",
      "14\n",
      "there is no data in 37241\n",
      "15\n",
      "there is no data in 24456\n",
      "16\n",
      "there is no data in 387638\n",
      "17\n",
      "there is no data in 387638\n",
      "18\n",
      "there is no data in 387638\n",
      "19\n",
      "there is no data in 387638\n",
      "20\n",
      "there is no data in 387638\n",
      "21\n",
      "there is no data in 387638\n",
      "22\n",
      "there is no data in 24456\n",
      "23\n",
      "there is no data in 37241\n",
      "24\n",
      "there is no data in 59200\n",
      "25\n",
      "sqft: 10779; years: 1970.0\n",
      "10779 sqft Office building, constructed in 1970\n",
      "-----------------------------------\n",
      "there is no data in 10779\n",
      "25\n",
      "there is no data in 200933\n",
      "26\n",
      "there is no data in 200933\n",
      "27\n",
      "there is no data in 200933\n",
      "28\n",
      "there is no data in 24456\n"
     ]
    }
   ],
   "source": [
    "temp_list = rerun_list[pop_index: ]\n",
    "# main loop to scrapping\n",
    "for test in rerun_list:\n",
    "    print(pop_index)\n",
    "    execute_path = {\"executable_path\": \"/usr/local/bin/chromedriver\"}\n",
    "    browser = Browser(\"chrome\", **execute_path, headless = False)\n",
    "    url_new = df_reset.loc[test,'html']\n",
    "    if_break = False\n",
    "    if type(url_new) != str:\n",
    "        if_break = True\n",
    "        for i in url_new:\n",
    "            browser.visit(i)\n",
    "            time.sleep(1)\n",
    "            html2 = browser.html\n",
    "            soup2 = bs(html2, 'html.parser')\n",
    "            txt = soup2.find_all('div', {'class': \"col-sm-7\"})[0].find_all('div')[1].text\n",
    "            txt = txt[-4:]\n",
    "            if txt == str(df_building.loc[test, 'year_built']):\n",
    "                url_new = i\n",
    "                if_break = False\n",
    "                break\n",
    "    if if_break:\n",
    "        pop_index += 1\n",
    "        print('there is no data in ' + str(test))\n",
    "        continue\n",
    "    browser.visit(url_new)\n",
    "    time.sleep(1)\n",
    "    html1 = browser.html\n",
    "    soup1 = bs(html1, 'html.parser')\n",
    "    print('sqft: ' + str(test) + '; years: ' + str(df_building.loc[test, 'year_built']))\n",
    "    print(soup1.find_all('div', {'class': \"col-sm-7\"})[0].find_all('div')[1].text)\n",
    "    print('-'*35)\n",
    "    browser.fill('start-date', '01/01/2017')\n",
    "    browser.fill('end-date', '01/01/2019')\n",
    "    browser.find_by_id('filetype').first.select('json')\n",
    "    try:\n",
    "        browser.find_by_value('Get Data').click()\n",
    "    except:\n",
    "        print('there is something wrong in ' + str(test))\n",
    "        break\n",
    "    html = browser.html\n",
    "    time.sleep(3)\n",
    "    soup = bs(html, 'html.parser')\n",
    "    browser.quit()\n",
    "    try:\n",
    "        json_real = json.loads(soup.text)\n",
    "    except:\n",
    "        print('there is no data in ' + str(test))\n",
    "        temp_list = sqf_list[pop_index: ]\n",
    "        continue\n",
    "    try:\n",
    "        df_temp_electric = pd.DataFrame(json_real[0]['values'])\n",
    "        df_temp_electric['building_id'] = df_building.loc[test, 'building_id']\n",
    "        df_temp_electric['meter'] = 0\n",
    "        df_electric = pd.concat([df_electric, df_temp_electric], ignore_index=True, sort=False)\n",
    "    except:\n",
    "        print('there is huge problem in ' + str(test))\n",
    "        break\n",
    "    try:\n",
    "        df_temp_water = pd.DataFrame(json_real[1]['values'])\n",
    "    except:\n",
    "        print('there is no water data in ' + str(test))\n",
    "    try:\n",
    "        df_temp_water['building_id'] = df_building.loc[test, 'building_id']\n",
    "        df_temp_water['meter'] = 5\n",
    "        df_water = pd.concat([df_water, df_temp_water], ignore_index=True, sort=False)\n",
    "    except:\n",
    "        print('there is huge problem of water data in ' + str(test))\n",
    "    print('*'*35)\n",
    "    print(str(df_electric.shape))\n",
    "    print(str(df_water.shape))\n",
    "    print('*'*35)\n",
    "    pop_index += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_electric.to_csv('../Large_output/csv/electric_site0.csv')\n",
    "df_water.to_csv('./Large_output/csv/water_site0.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
